--    Program: 		CAPCMEXT_CE_STATEMENT_RECON.sql
--    Description:	Extract CM Statement Reconciliation information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   

  l_processed_count_1 	number;
  l_rejected_count_1  	number;
  l_err_msg       	varchar2(2000);
  
  l_utl_file_1  		utl_file.file_type;

  l_filename_1 		varchar2(255);

  l_pathname  		varchar2(100);

  l_ngo_sob			varchar2(20);
  
  l_temp 			varchar2(10);

  l_bg_code			varchar2(10);

  cursor cur_1 (p_bg_code varchar2) is
    select csra.statement_line_id, 
	   csra.reference_type, 
	   csra.reference_id, 
	   csra.created_by, 
	   csra.creation_date, 
	   csra.last_updated_by,
	   csra.last_update_date, 
	   csra.je_header_id, 
	   csra.org_id, 
	   csra.reference_status, 
	   csra.status_flag, 
	   csra.action_flag, 
	   csra.current_record_flag, 
	   csra.auto_reconciled_flag, 
	   csra.amount, 
	   csra.request_id, 
	   csra.program_application_id, 
	   csra.program_id,
	   csra.program_update_date 
from ce_statement_reconcils_all csra, hr_all_organization_units haou, ce_statement_lines csl
where csra.org_id = haou.organization_id
and haou.name like 'OU_'||P_BG_CODE||'%'
and csra.statement_line_id = csl.statement_line_id
order by csra.rowid;


begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPCM_CE_STATEMENT_RECON.dat';
  
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);

  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
                                         
  l_ngo_sob := '&1';
  l_bg_code := substr(l_ngo_sob,(instr(l_ngo_sob,'C')),4);

  begin
 	select distinct substr(name,4,4) into l_temp
	 	from hr_all_organization_units
	 	where name like 'OU_'|| l_bg_code ||'%';

   exception
     when others then
       dbms_output.put_line(l_bg_code ||': BG name not found');
       raise;
   end;    
  
    for cur1 in cur_1(l_bg_code) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file_1, 
	  			cur1.statement_line_id || chr(9) ||
				cur1.reference_type || chr(9) ||
				cur1.reference_id || chr(9) ||
	  			cur1.created_by || chr(9) ||
	  			cur1.creation_date || chr(9) ||
	  			cur1.last_updated_by || chr(9) ||
	  			cur1.last_update_date || chr(9) ||
	  			cur1.je_header_id || chr(9) ||
	  			cur1.org_id || chr(9) ||
	  			cur1.reference_status || chr(9) ||
	  			cur1.status_flag || chr(9) ||
	  			cur1.action_flag || chr(9) ||
	  			cur1.current_record_flag || chr(9) ||
	  			cur1.auto_reconciled_flag || chr(9) ||
	  			cur1.amount || chr(9) ||
	  			cur1.request_id || chr(9) ||
	  			cur1.program_application_id || chr(9) ||
	  			cur1.program_id || chr(9) ||
	  			cur1.program_update_date              
	    );

  	    l_processed_count_1 := l_processed_count_1 + 1;
       end loop;
       	    
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_bg_code || ': bank statement reconciliation not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPCMEXT_CE_STATEMENT_RECON_ALL', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
   
   commit;
   
  utl_file.fclose(l_utl_file_1);
  
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
